The present invention is directed to computer databases. More particularly, the present invention is directed to the collection of computer database object statistics.
The performance of databases and database management systems (xe2x80x9cDBMSxe2x80x9ds) is critical to many corporations to meet the need of their customers. Customers desire timely and accurate information, and this information is frequently retrieved by an application program interfacing with a database. Poor performance of a database is usually due to inefficient structured query language (xe2x80x9cSQLxe2x80x9d) by the application program, or inefficient access path selection by the database and DBMS.
Most DBMSs, such as the DB2 relational DBMS from IBM Corp., use database object statistics to determine access paths. The access paths may be determined at plan bind time, or at execution time in the case of dynamic SQL. In either case, if the statistics are not up to date the access path selected may not be optimal.
Products are available for collecting statistics of database objects. Examples of these prior art products are xe2x80x9cRUNSTATSxe2x80x9d from IBM Corp., and the xe2x80x9cDatabase Analyzerxe2x80x9d from Platinum technology inc. However, one drawback with these products is that they must be run in batch mode, which is time consuming. A further drawback is that if the highest accuracy is desired, the database objects must be in a read-only state during statistics collection.
Based on the foregoing, there is a need for a system and method for collecting and maintaining database object statistics on a real-time basis without limiting the database objects to read-only status during collection.
One embodiment of the present invention is a system for collecting database object statistics by a database management system. The system selects one or more objects and corresponding one or more object fields and then creates a base set of statistics for the selected object fields. The system further reads active log records and updates the base set of statistics when a change is read in the active log records. Finally, the system begins extracting log records when an end-of-file of the active log records is reached, and updates the updated statistics based on the extracted log records.